package org.turbobytes.unikey.generator.table.dao.impl;

import static org.turbobytes.unikey.util.CollectionUtil.containsIgnoreCase;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.turbobytes.unikey.generator.table.TableConfiguration;
import org.turbobytes.unikey.generator.table.dao.TableIdGeneratorDao;
import org.turbobytes.unikey.helper.SqlRetriever;
import org.turbobytes.unikey.helper.impl.XPathSqlRetriever;
import org.turbobytes.unikey.util.db.DbException;
import org.turbobytes.unikey.util.db.DbUtil;
import org.turbobytes.unikey.util.db.metadata.DbType;
import org.turbobytes.unikey.util.db.metadata.Metadata;
import org.turbobytes.unikey.util.db.model.Column;
import org.turbobytes.unikey.util.db.model.ColumnType;

/**
 * IdGenerator needs to do 3 types of logic. Configuration Logic, Data Access
 * Logic and Id Generation Logic. This interface abstracts out Data Access Logic
 * into its own abstraction and provides a clean separation of concerns for
 * TableIdGenerator.
 * 
 * @author Ganeshji Marwaha
 * @since Aug 9, 2010
 */
public class TableIdGeneratorJdbcDao implements TableIdGeneratorDao {

  protected static final Log log = LogFactory.getLog(TableIdGeneratorJdbcDao.class);

  private TableConfiguration configuration;

  /**
   * Default constructor
   */
  public TableIdGeneratorJdbcDao() {
  }

  /**
   * Constructor that takes the TableConfiguration object
   * 
   * @param configuration
   */
  public TableIdGeneratorJdbcDao(TableConfiguration configuration) {
    this.configuration = configuration;
  }

  /**
   * Validates the current database with the user configuration. If all the
   * configuration is valid and already exists in the database as specified in
   * the configuration, nothing special is done here. But if there is an error a
   * DbException is thrown with an appropriate message.
   * 
   * @param connection
   * @throws SQLException
   */
  public void initDbIfNecessary(Connection connection) {
    Metadata meta = DbUtil.getMetadata(connection);
    String tableName = configuration.getTable();
    boolean isTablePresent = meta.isTablePresent(connection, tableName);
    if (isTablePresent) {
      log.debug("Table with name '" + tableName + "' exists. Continuing to check columns.");
      validateColumn(meta, connection, tableName, configuration.getContextNameColumn(), ColumnType.CONTEXT_NAME);
      validateColumn(meta, connection, tableName, configuration.getLastValueColumn(), ColumnType.LAST_VALUE);
    } else {
      log.debug("Table with name '" + tableName + "' doesn't exist.");
      if (configuration.isCreateTable()) {
        log.debug("Creating table '" + tableName + "'");
        try {
          createTable(connection);
        } catch (SQLException sqle) {
          log.error(sqle.getMessage());
          throw new DbException(sqle.getMessage());
        }
      } else {
        String message = "Cannot create table as the user configuration doesn't allow it";
        log.error(message);
        throw new DbException(message);
      }
    }

    log.debug("Init Db Complete. You are now ready to generate ids.");
  }

  /**
   * To create the table which maintains the key details if the table is not
   * available in the DB.
   * 
   * @param connection
   *          SQL Connection object
   * @throws SQLException
   *           if unable to create the table.
   */
  private void createTable(Connection connection) throws SQLException {
    Statement stmt = connection.createStatement();
    String mySQLCreate = getCreateScript(connection);

    mySQLCreate = replacePlaceHolders(mySQLCreate);
    log.debug("Creating table query -> '" + mySQLCreate + "'");
    stmt.executeUpdate(mySQLCreate);
    stmt.close();
  }

  /**
   * To replace the place holder value for Table and the column names.
   * 
   * @param originalQuery Externalized query which contains place holder
   * @return Updated query
   */
  private String replacePlaceHolders(String originalQuery) {
    String updatedQuery = originalQuery.replaceFirst("TABLE_NAME", configuration.getTable());
    updatedQuery = updatedQuery.replaceFirst("COLUMN_1", configuration.getContextNameColumn());
    updatedQuery = updatedQuery.replaceFirst("COLUMN_2", configuration.getLastValueColumn());
    log.debug("Query = " + originalQuery);
    return updatedQuery;
  }

  /**
   * 
   */
  public long getContextValue(Connection connection, String contextName) {
    String selectSql = null;
    PreparedStatement pStmt = null;
    ResultSet rs = null;
    long newContextValue = 0;
    try {
      selectSql = getSelectScript(connection);
      selectSql = replacePlaceHolders(selectSql);
      pStmt = connection.prepareStatement(selectSql);
      pStmt.setString(1, contextName);
      rs = pStmt.executeQuery();
      long existingContextValue = 0;

      if (rs.next()) {
        existingContextValue = rs.getLong(1);
        newContextValue = doUpdate(connection, contextName, existingContextValue);
      } else {
        newContextValue = doInsert(connection, contextName);
      }
    } catch (SQLException sqle) {
      log.error(sqle.getMessage());
      throw new DbException(sqle.getMessage());
    } finally {
      closeResources(pStmt, rs);
    }

    return newContextValue;
  }

  /**
   * 
   * @param stmt
   * @param rs
   */
  private void closeResources(Statement stmt, ResultSet rs) {
    if (rs != null) {
      try {
        rs.close();
      } catch (SQLException e) {
        // Suppress Exception
      }
    }

    if (stmt != null) {
      try {
        stmt.close();
      } catch (SQLException e) {
        // Suppress Exception
      }
    }

  }

  /**
   * 
   * @param connection
   * @param contextName
   * @param contextValue
   * @return
   * @throws SQLException
   */
  private long doUpdate(Connection connection, String contextName, long contextValue) throws SQLException {
    String dmlSql = getUpdateScript(connection);
    dmlSql = replacePlaceHolders(dmlSql);
    PreparedStatement pStmt = null;
    long newValue = 0;
    try {
      pStmt = connection.prepareStatement(dmlSql);

      newValue = contextValue + (configuration.getBatchSize() * configuration.getIncrementBy());

      pStmt.setLong(1, newValue);
      pStmt.setString(2, contextName);

      int updateCnt = pStmt.executeUpdate();

      if (updateCnt == 0) {
        throw new DbException("Unable to update the records");
      }
    } finally {
      closeResources(pStmt, null);
    }
    return newValue;
  }

  /**
   * 
   * @param connection
   * @param contextName
   * @return
   * @throws SQLException
   */
  private long doInsert(Connection connection, String contextName) throws SQLException {
    String dmlSql = getInsertScript(connection);
    dmlSql = replacePlaceHolders(dmlSql);
    PreparedStatement pStmt = null;

    try {
      pStmt = connection.prepareStatement(dmlSql);
      pStmt.setString(1, contextName);
      pStmt.setLong(2, configuration.getBatchSize());
      int updateCnt = pStmt.executeUpdate();

      if (updateCnt == 0) {
        throw new DbException("Unable to insert the records");
      }
    } finally {
      closeResources(pStmt, null);
    }
    return configuration.getBatchSize();
  }

  /**
   * 
   * @param connection
   * @return
   * @throws SQLException
   */
  private String getCreateScript(Connection connection) throws SQLException {
    SqlRetriever retriever = new XPathSqlRetriever();
    DbType dbType = DbUtil.getDbType(connection);
    return retriever.getCreateScript(dbType);
  }

  /**
   * 
   * @param connection
   * @return
   * @throws SQLException
   */
  private String getInsertScript(Connection connection) throws SQLException {
    SqlRetriever retriever = new XPathSqlRetriever();
    DbType dbType = DbUtil.getDbType(connection);
    return retriever.getInsertScript(dbType);
  }

  /**
   * 
   * @param connection
   * @return
   * @throws SQLException
   */
  private String getUpdateScript(Connection connection) throws SQLException {
    SqlRetriever retriever = new XPathSqlRetriever();
    DbType dbType = DbUtil.getDbType(connection);
    return retriever.getUpdateScript(dbType);
  }

  /**
   * 
   * @param connection
   * @return
   * @throws SQLException
   */
  private String getSelectScript(Connection connection) throws SQLException {
    SqlRetriever retriever = new XPathSqlRetriever();
    DbType dbType = DbUtil.getDbType(connection);
    return retriever.getSelectScript(dbType);
  }

  /**
   * Validates whether a given columnName is valid within the given tableName
   * and if it conforms to supported data types. If the validation fails a
   * DbException with an appropriate message is thrown.
   * 
   * @param meta
   * @param conn
   * @param tableName
   * @param columnName
   * @param cType
   */
  private void validateColumn(Metadata meta, Connection conn, String tableName, String columnName, ColumnType cType) {
    String message = "";

    // Validate Column Name
    Column column = meta.getColumn(conn, tableName, columnName);
    if (column == null) {
      message = "Column [" + columnName + "] does not exist in Table [" + tableName + "]. "
          + "Update your configuration to reflect the correct column name.";
      log.error(message);
      throw new DbException(message);
    }

    // Validate Column Type
    List<String> dbDataTypes = meta.getSupportedDbDataTypes(cType.getDataType());
    if (!containsIgnoreCase(dbDataTypes, column.getType())) {
      message = "Column [" + column + "] doesn't have the required datatype. " + "It has " + column.getType()
          + " but expected to have" + dbDataTypes;
      log.error(message);
      throw new DbException(message);
    }
  }

  /**
   * 
   */
  public TableConfiguration getConfiguration() {
    return configuration;
  }

  /**
   * 
   */
  public void setConfiguration(TableConfiguration configuration) {
    this.configuration = configuration;
  }
}
